package edu.jptest.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import edu.jptest.bean.ExaminationPaper;
import edu.jptest.util.GetDataBaseConnection;

public class ExaminationPaperDAO {

	// 插入试卷
	public boolean insertExaminationPaper(ExaminationPaper examinationPaper) {
		GetDataBaseConnection getDataBaseConnection = new GetDataBaseConnection();
		Connection connection = getDataBaseConnection.getConnection();
		PreparedStatement pstm = null;
		try {
			String sql = "insert into examine_paper(user_id,insert_time,do_times,unit_begin,unit_end,lesson_begin,lesson_end,paper_name,paper_origin,paper_type,paper_enabled) values(?,?,?,?,?,?,?,?,?,?,?)";
			pstm = connection.prepareStatement(sql);
			pstm.setInt(1, examinationPaper.getUserId());
			pstm.setDate(2, examinationPaper.getInsertTime());
			pstm.setInt(3, examinationPaper.getDoTimes());
			pstm.setInt(4, examinationPaper.getUnitBegin());
			pstm.setInt(5, examinationPaper.getUnitEnd());
			pstm.setInt(6, examinationPaper.getLessonBegin());
			pstm.setInt(7, examinationPaper.getLessonEnd());
			pstm.setString(8, examinationPaper.getPaperName());
			pstm.setString(9, examinationPaper.getPaperOrigin());
			pstm.setString(10, examinationPaper.getPaperType());
			pstm.setInt(11, examinationPaper.getPaperEnabled());
			int result = pstm.executeUpdate();
			if (result == 1) {
				return true;
			} else {
				return false;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (connection != null)
				try {
					connection.close();
				} catch (SQLException e) {
					;
				}
			connection = null;
			if (pstm != null) {
				try {
					pstm.close();
				} catch (SQLException e) {
					;
				}
			}
			pstm = null;
		}
		return false;
	}

	// 删除试卷
	public boolean deleteExaminationPaperr(int examinationPaper_id) {
		GetDataBaseConnection getDataBaseConnection = new GetDataBaseConnection();
		Connection connection = getDataBaseConnection.getConnection();
		PreparedStatement pstm = null;
		try {
			String sql = "delete from examine_paper where e_p_id=?";
			pstm = connection.prepareStatement(sql);
			pstm.setInt(1, examinationPaper_id);
			int result = pstm.executeUpdate();
			if (result == 1) {
				return true;
			} else {
				return false;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (connection != null)
				try {
					connection.close();
				} catch (SQLException e) {
					;
				}
			connection = null;
			if (pstm != null) {
				try {
					pstm.close();
				} catch (SQLException e) {
					;
				}
			}
			pstm = null;
		}
		return false;
	}

	// 更该试卷
	public boolean updateExaminationPaper(ExaminationPaper examinationPaper) {
		GetDataBaseConnection getDataBaseConnection = new GetDataBaseConnection();
		Connection connection = getDataBaseConnection.getConnection();
		PreparedStatement pstm = null;
		try {
			// user_id,insert_time,do_times,unit_begin,unit_end,lesson_begin,lesson_end,paper_name,paper_origin,paper_type,paper_enabled
			String sql = "update examine_paper set user_id=?,insert_time=?,do_times=?,unit_begin=?,unit_end=?,lesson_begin=?,lesson_end=?,paper_name=?,paper_origin=?,paper_type=?,paper_enabled=? where e_p_id=?";
			pstm = connection.prepareStatement(sql);
			pstm.setInt(1, examinationPaper.getUserId());
			pstm.setDate(2, examinationPaper.getInsertTime());
			pstm.setInt(3, examinationPaper.getDoTimes());
			pstm.setInt(4, examinationPaper.getUnitBegin());
			pstm.setInt(5, examinationPaper.getUnitEnd());
			pstm.setInt(6, examinationPaper.getLessonBegin());
			pstm.setInt(7, examinationPaper.getLessonEnd());
			pstm.setString(8, examinationPaper.getPaperName());
			pstm.setString(9, examinationPaper.getPaperOrigin());
			pstm.setString(10, examinationPaper.getPaperType());
			pstm.setInt(11, examinationPaper.getPaperEnabled());
			pstm.setInt(12, examinationPaper.getExaminationPaperId());
			int result = pstm.executeUpdate();
			if (result == 1) {
				return true;
			} else {
				return false;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (connection != null)
				try {
					connection.close();
				} catch (SQLException e) {
					;
				}
			connection = null;
			if (pstm != null) {
				try {
					pstm.close();
				} catch (SQLException e) {
					;
				}
			}
			pstm = null;
		}
		return false;
	}

	// 通过试卷id，查询试卷对象
	public ExaminationPaper getExaminationPaperByExaminationPaperId(
			int examinationPaper_id) {
		GetDataBaseConnection getDataBaseConnection = new GetDataBaseConnection();
		Connection connection = getDataBaseConnection.getConnection();
		ResultSet resultSet = null;
		ExaminationPaper examinationPaper = null;
		PreparedStatement pstm = null;
		try {
			String sql = "select * from examine_paper where e_p_id=?";
			pstm = connection.prepareStatement(sql);
			pstm.setInt(1, examinationPaper_id);
			resultSet = pstm.executeQuery();
			while (resultSet.next()) {
				examinationPaper.setExaminationPaperId(resultSet.getInt(1));
				examinationPaper.setUserId(resultSet.getInt(2));
				examinationPaper.setInsertTime(resultSet.getDate(3));
				examinationPaper.setDoTimes(resultSet.getInt(4));
				examinationPaper.setUnitBegin(resultSet.getInt(5));
				examinationPaper.setUnitEnd(resultSet.getInt(6));
				examinationPaper.setLessonBegin(resultSet.getInt(7));
				examinationPaper.setLessonEnd(resultSet.getInt(8));
				examinationPaper.setPaperName(resultSet.getString(9));
				examinationPaper.setPaperOrigin(resultSet.getString(10));
				examinationPaper.setPaperType(resultSet.getString(11));
				examinationPaper.setPaperEnabled(resultSet.getInt(12));
				return examinationPaper;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (connection != null)
				try {
					connection.close();
				} catch (SQLException e) {
					;
				}
			connection = null;
			if (pstm != null) {
				try {
					pstm.close();
				} catch (SQLException e) {
					;
				}
			}
			pstm = null;
			if (resultSet != null) {
				try {
					resultSet.close();
				} catch (SQLException e) {
					;
				}
			}
			resultSet = null;
		}
		return new ExaminationPaper();
	}

	// 通过组卷人，获得此人组过的试卷id
	public List<Integer> getExaminationPaperByPerson(int user_id) {
		GetDataBaseConnection getDataBaseConnection = new GetDataBaseConnection();
		Connection connection = getDataBaseConnection.getConnection();
		ResultSet resultSet = null;
		PreparedStatement pstm = null;
		List<Integer> examinePapresId = new ArrayList<Integer>();
		try {
			String sql = "select e_p_id from examine_paper where user_id=?";
			pstm = connection.prepareStatement(sql);
			pstm.setInt(1, user_id);
			resultSet = pstm.executeQuery();
			while (resultSet.next()) {
				examinePapresId.add(resultSet.getInt(1));
			}
			return examinePapresId;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (connection != null)
				try {
					connection.close();
				} catch (SQLException e) {
					;
				}
			connection = null;
			if (pstm != null) {
				try {
					pstm.close();
				} catch (SQLException e) {
					;
				}
			}
			pstm = null;
			if (resultSet != null) {
				try {
					resultSet.close();
				} catch (SQLException e) {
					;
				}
			}
			resultSet = null;
		}
		return null;
	}

	// 通过组卷时间，获得此时间组过的试卷id
	public int getExaminationPaperByTime(java.sql.Date time) {
		GetDataBaseConnection getDataBaseConnection = new GetDataBaseConnection();
		Connection connection = getDataBaseConnection.getConnection();
		ResultSet resultSet = null;
		PreparedStatement pstm = null;
		try {
			String sql = "select e_p_id from examine_paper where insert_time=?";
			pstm = connection.prepareStatement(sql);
			pstm.setDate(1, time);
			resultSet = pstm.executeQuery();
			while (resultSet.next()) {
				return resultSet.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (connection != null)
				try {
					connection.close();
				} catch (SQLException e) {
					;
				}
			connection = null;
			if (pstm != null) {
				try {
					pstm.close();
				} catch (SQLException e) {
					;
				}
			}
			pstm = null;
			if (resultSet != null) {
				try {
					resultSet.close();
				} catch (SQLException e) {
					;
				}
			}
			resultSet = null;
		}
		return 0;
	}

	// 通过试卷类型，获得此类型的试卷id
	public List<Integer> getExaminationPaperByType(String type) {
		GetDataBaseConnection getDataBaseConnection = new GetDataBaseConnection();
		Connection connection = getDataBaseConnection.getConnection();
		ResultSet resultSet = null;
		PreparedStatement pstm = null;
		List<Integer> examinePapresId = new ArrayList<Integer>();
		try {
			String sql = "select e_p_id from examine_paper where paper_type=?";
			pstm = connection.prepareStatement(sql);
			pstm.setString(1, type);
			resultSet = pstm.executeQuery();
			while (resultSet.next()) {
				examinePapresId.add(resultSet.getInt(1));
			}
			return examinePapresId;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (connection != null)
				try {
					connection.close();
				} catch (SQLException e) {
					;
				}
			connection = null;
			if (pstm != null) {
				try {
					pstm.close();
				} catch (SQLException e) {
					;
				}
			}
			pstm = null;
			if (resultSet != null) {
				try {
					resultSet.close();
				} catch (SQLException e) {
					;
				}
			}
			resultSet = null;
		}
		return null;
	}

	// 获得被做过的次数排在前十名的试卷id
	public List<Integer> getExaminationPaperByDoTimes() {
		GetDataBaseConnection getDataBaseConnection = new GetDataBaseConnection();
		Connection connection = getDataBaseConnection.getConnection();
		ResultSet resultSet = null;
		PreparedStatement pstm = null;
		List<Integer> examinePapresId = new ArrayList<Integer>();
		try {
			String sql = "select e_p_id from examine_paper order by do_times desc limit 10";
			pstm = connection.prepareStatement(sql);
			resultSet = pstm.executeQuery();
			while (resultSet.next()) {
				examinePapresId.add(resultSet.getInt(1));
			}
			return examinePapresId;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (connection != null)
				try {
					connection.close();
				} catch (SQLException e) {
					;
				}
			connection = null;
			if (pstm != null) {
				try {
					pstm.close();
				} catch (SQLException e) {
					;
				}
			}
			pstm = null;
			if (resultSet != null) {
				try {
					resultSet.close();
				} catch (SQLException e) {
					;
				}
			}
			resultSet = null;
		}
		return null;
	}

	// 通过单元区间，获得此区间内的试卷id
	public List<Integer> getExaminationPaperByUnitScope(int unitStart,
			int unitEnd) {
		GetDataBaseConnection getDataBaseConnection = new GetDataBaseConnection();
		Connection connection = getDataBaseConnection.getConnection();
		ResultSet resultSet = null;
		PreparedStatement pstm = null;
		List<Integer> examinePapresId = new ArrayList<Integer>();
		try {
			String sql = "select e_p_id from examine_paper where unit_begin=? and unit_end=?";// ////
			pstm = connection.prepareStatement(sql);
			pstm.setInt(1, unitStart);
			pstm.setInt(2, unitEnd);
			resultSet = pstm.executeQuery();
			while (resultSet.next()) {
				examinePapresId.add(resultSet.getInt(1));
			}
			return examinePapresId;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (connection != null)
				try {
					connection.close();
				} catch (SQLException e) {
					;
				}
			connection = null;
			if (pstm != null) {
				try {
					pstm.close();
				} catch (SQLException e) {
					;
				}
			}
			pstm = null;
			if (resultSet != null) {
				try {
					resultSet.close();
				} catch (SQLException e) {
					;
				}
			}
			resultSet = null;
		}
		return null;
	}

	// 通过课别区间，获得此区间内的试卷id
	public List<Integer> getExaminationPaperByLessonScope(int lessonStart,
			int lessonEnd) {
		GetDataBaseConnection getDataBaseConnection = new GetDataBaseConnection();
		Connection connection = getDataBaseConnection.getConnection();
		ResultSet resultSet = null;
		PreparedStatement pstm = null;
		List<Integer> examinePapresId = new ArrayList<Integer>();
		try {
			String sql = "select e_p_id from examine_paper where lesson_begin=? and lesson_end=?";// ////
			pstm = connection.prepareStatement(sql);
			pstm.setInt(1, lessonStart);
			pstm.setInt(2, lessonEnd);
			resultSet = pstm.executeQuery();
			while (resultSet.next()) {
				examinePapresId.add(resultSet.getInt(1));
			}
			return examinePapresId;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (connection != null)
				try {
					connection.close();
				} catch (SQLException e) {
					;
				}
			connection = null;
			if (pstm != null) {
				try {
					pstm.close();
				} catch (SQLException e) {
					;
				}
			}
			pstm = null;
			if (resultSet != null) {
				try {
					resultSet.close();
				} catch (SQLException e) {
					;
				}
			}
			resultSet = null;
		}
		return null;
	}

	// 通过试卷来源，获得已组的此来源的试卷id
	public List<Integer> getExaminationPaperByUnit(String paperOrigin) {
		GetDataBaseConnection getDataBaseConnection = new GetDataBaseConnection();
		Connection connection = getDataBaseConnection.getConnection();
		ResultSet resultSet = null;
		PreparedStatement pstm = null;
		List<Integer> examinePapresId = new ArrayList<Integer>();
		try {
			String sql = "select e_p_id from examine_paper where paper_origin=?";
			pstm = connection.prepareStatement(sql);
			pstm.setString(1, paperOrigin);
			resultSet = pstm.executeQuery();
			while (resultSet.next()) {
				examinePapresId.add(resultSet.getInt(1));
			}
			return examinePapresId;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (connection != null)
				try {
					connection.close();
				} catch (SQLException e) {
					;
				}
			connection = null;
			if (pstm != null) {
				try {
					pstm.close();
				} catch (SQLException e) {
					;
				}
			}
			pstm = null;
			if (resultSet != null) {
				try {
					resultSet.close();
				} catch (SQLException e) {
					;
				}
			}
			resultSet = null;
		}
		return null;
	}

	// 查看已开放的试卷id
	public List<Integer> getIsOpnedExaminationPapersId() {
		GetDataBaseConnection getDataBaseConnection = new GetDataBaseConnection();
		Connection connection = getDataBaseConnection.getConnection();
		ResultSet resultSet = null;
		PreparedStatement pstm = null;
		List<Integer> examinePapers = new ArrayList<Integer>();
		try {
			String sql = "select e_p_id from examine_paper where paper_enabled=1";
			pstm = connection.prepareStatement(sql);
			resultSet = pstm.executeQuery();
			while (resultSet.next()) {
				examinePapers.add(resultSet.getInt(1));
			}
			return examinePapers;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (connection != null)
				try {
					connection.close();
				} catch (SQLException e) {
					;
				}
			connection = null;
			if (pstm != null) {
				try {
					pstm.close();
				} catch (SQLException e) {
					;
				}
			}
			pstm = null;
			if (resultSet != null) {
				try {
					resultSet.close();
				} catch (SQLException e) {
					;
				}
			}
			resultSet = null;
		}
		return null;
	}

	// 获得所有试卷id
	public List<Integer> getAllExaminationPapersId() {
		GetDataBaseConnection getDataBaseConnection = new GetDataBaseConnection();
		Connection connection = getDataBaseConnection.getConnection();
		ResultSet resultSet = null;
		PreparedStatement pstm = null;
		List<Integer> examinePapers = new ArrayList<Integer>();
		try {
			String sql = "select e_p_id from examine_paper";
			pstm = connection.prepareStatement(sql);
			resultSet = pstm.executeQuery();
			while (resultSet.next()) {
				examinePapers.add(resultSet.getInt(1));
			}
			return examinePapers;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (connection != null)
				try {
					connection.close();
				} catch (SQLException e) {
					;
				}
			connection = null;
			if (pstm != null) {
				try {
					pstm.close();
				} catch (SQLException e) {
					;
				}
			}
			pstm = null;
			if (resultSet != null) {
				try {
					resultSet.close();
				} catch (SQLException e) {
					;
				}
			}
			resultSet = null;
		}
		return null;
	}

	// 通过试卷名称，获得此时间组过的试卷id
	public int getExaminationPaperByName(String paperName) {
		GetDataBaseConnection getDataBaseConnection = new GetDataBaseConnection();
		Connection connection = getDataBaseConnection.getConnection();
		ResultSet resultSet = null;
		PreparedStatement pstm = null;
		try {
			String sql = "select e_p_id from examine_paper where paper_name=?";
			pstm = connection.prepareStatement(sql);
			pstm.setString(1, paperName);
			resultSet = pstm.executeQuery();
			while (resultSet.next()) {
				return resultSet.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (connection != null)
				try {
					connection.close();
				} catch (SQLException e) {
					;
				}
			connection = null;
			if (pstm != null) {
				try {
					pstm.close();
				} catch (SQLException e) {
					;
				}
			}
			pstm = null;
			if (resultSet != null) {
				try {
					resultSet.close();
				} catch (SQLException e) {
					;
				}
			}
			resultSet = null;
		}
		return 0;
	}
}
